package dao;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import news.News;
import news.Tool;
import toolDAO.Page;

public class NewsDAO implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = -2011764412251426232L;
	/**
	 * @param args
	 */
	public static NewsDAO DAO = new NewsDAO();
	public static final int PAGESIZE = 10;

	public static List<News> find(int pageNo, int pageSize) {// 根据传入的页号显示指定书目的新闻
		List<News> list = new ArrayList<News>();
		News s = null;
		Connection con = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		String sql = "select nid,ntitle,nmodifyDate,ntid from news  order by nmodifyDate desc limit ?,? ";
		try {
			con = Tool.open();
			st = con.prepareStatement(sql);
			st.setInt(1, pageNo);
			st.setInt(2, pageSize);
			rs = st.executeQuery();
			while (rs.next()) {
				s = new News();
				s.setNid(rs.getInt("nid"));
				s.setNtitle(rs.getString("ntitle"));
				s.setNmodifyDate(rs.getTimestamp("nmodifyDate"));
				s.setNtid(rs.getInt("ntid"));
				list.add(s);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);

		} finally {
			Tool.close(st, con, rs);
		}
		return list;
	}

	public int del(int id) {// 删除新闻
		String sql = "delete from news where nid=?";
		int k = Tool.executeUpdate(sql, id);
		return k;
	}

	public int update(News s) {// 修改新闻
		String sql = "update news set ntid=?,ntitle=?,nauthor=?,nsummary=?,ncontent=?,npicPath=? where nid=?";
		int k = Tool.executeUpdate(sql, s.getNtid(), s.getNtitle(),
				s.getNauthor(), s.getNsummary(), s.getNcontent(),
				s.getNpicPath(), s.getNid());
		return k;
	}

	public int insert(News s) {// 新增新闻
		String sql = "insert into news(ntid,ntitle,nauthor,nsummary,ncontent,npicPath,ncreateDate) values(?,?,?,?,?,?,?)";
		int k = Tool.executeUpdate(sql, s.getNtid(), s.getNtitle(),
				s.getNauthor(), s.getNsummary(), s.getNcontent(),
				s.getNpicPath(), s.getNcreateDate());
		return k;
	}

	public Page<News> find(int pageNo) {// 分页显示新闻
		if (pageNo < 1) {
			pageNo = 1;
		}
		String sql1 = "select count(1) from news";// 总的news数目
		String sql2 = "select nid,ntitle,nmodifyDate,nauthor,ntid from news order by nmodifyDate desc limit ?,?";
		List<News> l = new ArrayList<News>();// 新建一个新闻集合
		Connection con = Tool.open();
		java.sql.PreparedStatement ps = null;
		ResultSet rs = null;
		int total = 0;
		News n = null;
		Page<News> page = new Page<News>();// 用来存放分页信息的
		try {
			ps = con.prepareStatement(sql1);

			rs = ps.executeQuery();
			if (rs.next()) {
				total = rs.getInt(1);
			}
			Tool.close(ps, null, rs);
			ps = con.prepareStatement(sql2);
			ps.setInt(1, (pageNo - 1) * PAGESIZE);
			ps.setInt(2, PAGESIZE);
			rs = ps.executeQuery();
			while (rs.next()) {
				n = new News();
				n.setNid(rs.getInt("nid"));
				n.setNtitle(rs.getString("ntitle"));
				n.setNmodifyDate(rs.getTimestamp("nmodifyDate"));
				n.setNauthor(rs.getString("nauthor"));
				n.setNtid(rs.getInt("ntid"));
				l.add(n);
			}
			int pageTotals = total / PAGESIZE + (total % PAGESIZE == 0 ? 0 : 1);
			page.setPageNums(pageTotals);
			page.setList(l);
			page.setPageSize(PAGESIZE);
			page.setPageNo(pageNo);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			Tool.close(ps, null, rs);
		}
		return page;
	}

	public Page<News> find1(int pageNo, Integer tid) {// 根据新闻的页号和新闻的分类号显示对应的新闻，页面初始化的时候显示当前页面
		if (pageNo < 1) {
			pageNo = 1;
		}
		String sql1 = "select count(1) from news";
		String sql2 = "select nid,ntitle,nmodifyDate,nauthor,ntid from news ";
		String where = "";
		List<Object> params = new ArrayList<Object>();
		if (tid != null) {
			params.add(tid);
			where = " where ntid=?";
		}
		sql1 = sql1 + where;
		sql2 = sql2 + where + " order by nmodifyDate desc limit ?,?";
		java.util.List<News> l = new ArrayList<News>();
		Connection con = Tool.open();
		java.sql.PreparedStatement ps = null;
		ResultSet rs = null;
		int total = 0;
		Page<News> page = new Page<News>();
		try {
			ps = con.prepareStatement(sql1);
			for (int i = 0; i < params.size(); i++) {
				ps.setObject(i + 1, params.get(i));
			}
			rs = ps.executeQuery();
			if (rs.next()) {
				total = rs.getInt(1);
			}
			Tool.close(ps, null, rs);
			params.add((pageNo - 1) * PAGESIZE);
			params.add(PAGESIZE);

			ps = con.prepareStatement(sql2);
			for (int i = 0; i < params.size(); i++) {
				ps.setObject(i + 1, params.get(i));
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				News n = new News();
				n.setNid(rs.getInt("nid"));
				n.setNtitle(rs.getString("ntitle"));
				n.setNmodifyDate(rs.getTimestamp("nmodifyDate"));
				n.setNauthor(rs.getString("nauthor"));

				l.add(n);
			}
			int pageTotals = total / PAGESIZE + (total % PAGESIZE == 0 ? 0 : 1);
			page.setPageNums(pageTotals);
			page.setList(l);
			page.setPageSize(PAGESIZE);
			page.setPageNo(pageNo);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			Tool.close(ps, null, rs);
		}
		return page;
	}

	public static List<News> show(int tid) {// 根据新闻分类显示全部新闻
		List<News> list = new ArrayList<News>();
		String sql = "select * from news where ntid=?";
		Connection con = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			con = Tool.open();
			st = con.prepareStatement(sql);
			st.setInt(1, tid);
			rs = st.executeQuery();
			while (rs.next()) {
				News n = new News();
				n.setNid(rs.getInt("nid"));
				n.setNtitle(rs.getString("ntitle"));
				n.setNmodifyDate(rs.getTimestamp("nmodifyDate"));
				n.setNauthor(rs.getString("nauthor"));
				n.setNtid(rs.getInt("ntid"));
				list.add(n);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);

		} finally {
			Tool.close(st, con, rs);
		}
		return list;
	}

	public static List<News> find(int pageNo, int pageSize, int ntid) {// 置顶的
		List<News> list = new ArrayList<News>();
		News s = null;
		Connection con = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		String sql = "select nid,ntitle,nmodifyDate,ntid from news where ntid=? order by nmodifyDate desc limit ?,? ";
		try {
			con = Tool.open();
			st = con.prepareStatement(sql);
			st.setInt(1, ntid);
			st.setInt(2, pageNo);
			st.setInt(3, pageSize);
			rs = st.executeQuery();
			while (rs.next()) {
				s = new News();
				s.setNid(rs.getInt("nid"));
				s.setNtitle(rs.getString("ntitle"));
				s.setNmodifyDate(rs.getTimestamp("nmodifyDate"));
				s.setNtid(rs.getInt("ntid"));
				list.add(s);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);

		} finally {
			Tool.close(st, con, rs);
		}
		return list;
	}

	public static News showNews(int nid) {// 返回单条新闻
		String sql = "select * from news where nid=?";
		Connection con = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		News n = null;
		try {
			con = Tool.open();
			st = con.prepareStatement(sql);
			st.setInt(1, nid);
			rs = st.executeQuery();
			while (rs.next()) {
				n = new News();
				n.setNid(nid);
				n.setNtitle(rs.getString("ntitle"));
				n.setNmodifyDate(rs.getTimestamp("nmodifyDate"));
				n.setNauthor(rs.getString("nauthor"));
				n.setNsummary(rs.getString("nsummary"));
				n.setNcontent(rs.getString("ncontent"));
				n.setNtid(rs.getInt("ntid"));
				n.setNpicPath(rs.getString("npicPath"));
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);

		} finally {
			Tool.close(st, con, rs);
		}
		return n;
	}

}
